--    Program: 		CAPARIMP_TRANSATCTION_TYPE.sql
--    Description:  Import AR Transaction Type information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);  
   l_err_msg_tmp			varchar2(2000); 
   
   l_cust_trx_type_id		number;
                    		                 		
   cursor cur_tran_type is
      select * from CRST_RA_CUST_TRX_TYPES_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_tran_type loop  
   	 l_err_msg_tmp := ''; 
   	 
   	 select ra_cust_trx_types_s.nextval
   	 into   l_cust_trx_type_id
   	 from   dual;
 
     update crst_ra_cust_trx_types_all
     set    cust_trx_type_id_n = l_cust_trx_type_id,
            org_id_n = CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
     where  cust_trx_type_id = cur.CUST_TRX_TYPE_ID; 
   	 
   	 begin 	     
       -- import data
       insert into RA_CUST_TRX_TYPES_ALL
       (
         CUST_TRX_TYPE_ID               ,                                                                      
         LAST_UPDATE_DATE               ,                                                                      
         LAST_UPDATED_BY                ,                                                                      
         CREATION_DATE                  ,                                                                      
         CREATED_BY                     ,                                                                      
         LAST_UPDATE_LOGIN              ,                                                                      
         POST_TO_GL                     ,                                                                      
         ACCOUNTING_AFFECT_FLAG         ,                                                                      
         CREDIT_MEMO_TYPE_ID            ,                                                                      
         STATUS                         ,                                                                      
         NAME                           ,                                                                      
         DESCRIPTION                    ,                                                                      
         TYPE                           ,                                                                      
         DEFAULT_TERM                   ,                                                                      
         DEFAULT_PRINTING_OPTION        ,                                                                      
         DEFAULT_STATUS                 ,                                                                      
         GL_ID_REV                      ,                                                                      
         GL_ID_FREIGHT                  ,                                                                      
         GL_ID_REC                      ,                                                                      
         SUBSEQUENT_TRX_TYPE_ID         ,                                                                      
         SET_OF_BOOKS_ID                ,                                                                      
         ATTRIBUTE_CATEGORY             ,                                                                      
         ATTRIBUTE1                     ,                                                                      
         ATTRIBUTE2                     ,                                                                      
         ATTRIBUTE3                     ,                                                                      
         ATTRIBUTE4                     ,                                                                      
         ATTRIBUTE5                     ,                                                                      
         ATTRIBUTE6                     ,                                                                      
         ATTRIBUTE7                     ,                                                                      
         ATTRIBUTE8                     ,                                                                      
         ATTRIBUTE9                     ,                                                                      
         ATTRIBUTE10                    ,                                                                      
         ALLOW_FREIGHT_FLAG             ,                                                                      
         ALLOW_OVERAPPLICATION_FLAG     ,                                                                      
         CREATION_SIGN                  ,                                                                      
         END_DATE                       ,                                                                      
         GL_ID_CLEARING                 ,                                                                      
         GL_ID_TAX                      ,                                                                      
         GL_ID_UNBILLED                 ,                                                                      
         GL_ID_UNEARNED                 ,                                                                      
         START_DATE                     ,                                                                      
         TAX_CALCULATION_FLAG           ,                                                                      
         ATTRIBUTE11                    ,                                                                      
         ATTRIBUTE12                    ,                                                                      
         ATTRIBUTE13                    ,                                                                      
         ATTRIBUTE14                    ,                                                                      
         ATTRIBUTE15                    ,                                                                      
         NATURAL_APPLICATION_ONLY_FLAG  ,                                                                      
         ORG_ID                         ,
         GLOBAL_ATTRIBUTE1              ,                                                                      
         GLOBAL_ATTRIBUTE2              ,                                                                      
         GLOBAL_ATTRIBUTE3              ,                                                                      
         GLOBAL_ATTRIBUTE4              ,                                                                      
         GLOBAL_ATTRIBUTE5              ,                                                                      
         GLOBAL_ATTRIBUTE6              ,                                                                      
         GLOBAL_ATTRIBUTE7              ,                                                                      
         GLOBAL_ATTRIBUTE8              ,                                                                      
         GLOBAL_ATTRIBUTE9              ,                                                                      
         GLOBAL_ATTRIBUTE10             ,                                                                      
         GLOBAL_ATTRIBUTE11             ,                                                                      
         GLOBAL_ATTRIBUTE12             ,                                                                      
         GLOBAL_ATTRIBUTE13             ,                                                                      
         GLOBAL_ATTRIBUTE14             ,                                                                      
         GLOBAL_ATTRIBUTE15             ,                                                                      
         GLOBAL_ATTRIBUTE16             ,                                                                      
         GLOBAL_ATTRIBUTE17             ,                                                                      
         GLOBAL_ATTRIBUTE18             ,                                                                      
         GLOBAL_ATTRIBUTE19             ,                                                                      
         GLOBAL_ATTRIBUTE20             ,                                                                      
         GLOBAL_ATTRIBUTE_CATEGORY      ,                                                                      
         RULE_SET_ID                    ,                                                                      
         SIGNED_FLAG                    ,                                                                      
         DRAWEE_ISSUED_FLAG             ,                                                                      
         MAGNETIC_FORMAT_CODE           ,                                                                      
         FORMAT_PROGRAM_ID              ,                                                                      
         GL_ID_UNPAID_REC               ,                                                                      
         GL_ID_REMITTANCE               ,                                                                      
         GL_ID_FACTOR                   ,                                                                      
         ALLOCATE_TAX_FREIGHT 
       )
       values
       (
         l_cust_trx_type_id                 ,                                                                      
         cur.LAST_UPDATE_DATE               ,                                                                      
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),                                                                      
         cur.CREATION_DATE                  ,                                                                      
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),                                                                      
         cur.LAST_UPDATE_LOGIN              ,                                                                      
         cur.POST_TO_GL                     ,                                                                      
         cur.ACCOUNTING_AFFECT_FLAG         ,                                                                      
         cur.CREDIT_MEMO_TYPE_ID            ,                                                                      
         cur.STATUS                         ,                                                                      
         cur.NAME                           ,                                                                      
         cur.DESCRIPTION                    ,                                                                      
         cur.TYPE                           ,                                                                      
         cur.DEFAULT_TERM                   ,                                                                      
         cur.DEFAULT_PRINTING_OPTION        ,                                                                      
         cur.DEFAULT_STATUS                 ,                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REV),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_FREIGHT),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REC),                                                                      
         cur.SUBSEQUENT_TRX_TYPE_ID         ,                                                                      
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),                                                                      
         cur.ATTRIBUTE_CATEGORY             ,                                                                      
         cur.ATTRIBUTE1                     ,                                                                      
         cur.ATTRIBUTE2                     ,                                                                      
         cur.ATTRIBUTE3                     ,                                                                      
         cur.ATTRIBUTE4                     ,                                                                      
         cur.ATTRIBUTE5                     ,                                                                      
         cur.ATTRIBUTE6                     ,                                                                      
         cur.ATTRIBUTE7                     ,                                                                      
         cur.ATTRIBUTE8                     ,                                                                      
         cur.ATTRIBUTE9                     ,                                                                      
         cur.ATTRIBUTE10                    ,                                                                      
         cur.ALLOW_FREIGHT_FLAG             ,                                                                      
         cur.ALLOW_OVERAPPLICATION_FLAG     ,                                                                      
         cur.CREATION_SIGN                  ,                                                                      
         cur.END_DATE                       ,                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_CLEARING),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_TAX),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_UNBILLED),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_UNEARNED),                                                                      
         cur.START_DATE                     ,                                                                      
         cur.TAX_CALCULATION_FLAG           ,                                                                      
         cur.ATTRIBUTE11                    ,                                                                      
         cur.ATTRIBUTE12                    ,                                                                      
         cur.ATTRIBUTE13                    ,                                                                      
         cur.ATTRIBUTE14                    ,                                                                      
         cur.ATTRIBUTE15                    ,                                                                      
         cur.NATURAL_APPLICATION_ONLY_FLAG  ,                                                                      
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.GLOBAL_ATTRIBUTE1              ,                                                                      
         cur.GLOBAL_ATTRIBUTE2              ,                                                                      
         cur.GLOBAL_ATTRIBUTE3              ,                                                                      
         cur.GLOBAL_ATTRIBUTE4              ,                                                                      
         cur.GLOBAL_ATTRIBUTE5              ,                                                                      
         cur.GLOBAL_ATTRIBUTE6              ,                                                                      
         cur.GLOBAL_ATTRIBUTE7              ,                                                                      
         cur.GLOBAL_ATTRIBUTE8              ,                                                                      
         cur.GLOBAL_ATTRIBUTE9              ,                                                                      
         cur.GLOBAL_ATTRIBUTE10             ,                                                                      
         cur.GLOBAL_ATTRIBUTE11             ,                                                                      
         cur.GLOBAL_ATTRIBUTE12             ,                                                                      
         cur.GLOBAL_ATTRIBUTE13             ,                                                                      
         cur.GLOBAL_ATTRIBUTE14             ,                                                                      
         cur.GLOBAL_ATTRIBUTE15             ,                                                                      
         cur.GLOBAL_ATTRIBUTE16             ,                                                                      
         cur.GLOBAL_ATTRIBUTE17             ,                                                                      
         cur.GLOBAL_ATTRIBUTE18             ,                                                                      
         cur.GLOBAL_ATTRIBUTE19             ,                                                                      
         cur.GLOBAL_ATTRIBUTE20             ,                                                                      
         cur.GLOBAL_ATTRIBUTE_CATEGORY      ,                                                                      
         cur.RULE_SET_ID                    ,                                                                      
         cur.SIGNED_FLAG                    ,                                                                      
         cur.DRAWEE_ISSUED_FLAG             ,                                                                      
         cur.MAGNETIC_FORMAT_CODE           ,                                                                      
         cur.FORMAT_PROGRAM_ID              ,                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_UNPAID_REC),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REMITTANCE),                                                                      
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_FACTOR),                                                                      
         cur.ALLOCATE_TAX_FREIGHT 
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   	 
   	 update ra_cust_trx_types_all tt
   	 set    credit_memo_type_id = (select ctt1.cust_trx_type_id_n
   	                               from   crst_ra_cust_trx_types_all ctt1
   	                               where  ctt1.cust_trx_type_id = tt.credit_memo_type_id
   	                               and    ctt1.org_id = tt.org_id)
   	 where  tt.credit_memo_type_id is not null
   	 and    exists (select 'X'
   	                from   crst_ra_cust_trx_types_all ctt2
   	                where  ctt2.cust_trx_type_id = tt.credit_memo_type_id
   	                and    ctt2.org_id = tt.org_id);
   	 
   	 commit;
   else
     rollback;
   end if;
		 
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_TRANSACTION_TYPE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit